package org.soenea.doitall.domain.model.group.data;

import java.sql.PreparedStatement;
import java.sql.SQLException;

import org.dsrg.soenea.service.logging.SQLLogger;
import org.dsrg.soenea.service.threadLocal.DbRegistry;

public class GroupTDG {
	public static final String BASE_NAME = "group";
	public static final String TABLE = DbRegistry.getTablePrefix()+BASE_NAME;

	public static final String INSERT = "INSERT INTO " + TABLE + "(groupID, groupName, description, version) VALUES(?, ?, ?, 1);";
	public static final String UPDATE = "UPDATE " + TABLE + " AS g set g.version=g.version+1, g.groupName=?, g.description=? WHERE g.groupID=? AND g.version=?;";
	public static final String DELETE = "DELETE FROM " + TABLE + " WHERE groupId=? AND version=?;";
	
	public final static String CREATE_TABLE = 
			"CREATE TABLE IF NOT EXISTS " + TABLE + " (" +
			"groupID BIGINT,"+
			"groupName VARCHAR(64),"+
			"description VARCHAR(128),"+
			"version INT,"+
			"PRIMARY KEY(groupID)"+
			") ENGINE=InnoDB;";
			  
	public final static String DROP_TABLE = "DROP TABLE IF EXISTS " + TABLE + ";";

	public static void createTable() throws SQLException {
		SQLLogger.processUpdate(DbRegistry.getDbConnection().createStatement(), CREATE_TABLE);
	}
	
	public static void dropTable() throws SQLException {
		SQLLogger.processUpdate(DbRegistry.getDbConnection().createStatement(), DROP_TABLE);
	}
	
	public static void insert(long id, String name, String description) throws SQLException {
		PreparedStatement ps = DbRegistry.getDbConnection().prepareStatement(INSERT);
		ps.setLong(1, id);
		ps.setString(2, name);
		ps.setString(3, description);
		ps.execute();
		ps.close();
	}
	
	public static int delete(long id, long version) throws SQLException {
		PreparedStatement ps = DbRegistry.getDbConnection().prepareStatement(DELETE);
		ps.setLong(1, id);
		ps.setLong(2, version);
		int count = ps.executeUpdate();
		ps.close();
		return count;
	}
	
	public static int update(long groupID, long version, String groupName, String description) throws SQLException {
		PreparedStatement ps = DbRegistry.getDbConnection().prepareStatement(UPDATE);
		ps.setString(1, groupName);
		ps.setString(2, description);
		ps.setLong(3, groupID);
		ps.setLong(4, version);
		ps.execute();		
		
		int count = ps.getUpdateCount();
		ps.close();
		return count;
	}	
}
